package com.gem.growingtree.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.web.bind.annotation.RequestMapping;
import com.gem.growingtree.db.DBLink;
import com.gem.growingtree.model.PageBean2;
import com.gem.growingtree.model.ShowTeachers;
import com.gem.growingtree.model.TeacherAccount;

public class TeacherAccountService {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	public void close(){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(pstmt != null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//显示所有老师信息
	public PageBean2 findByPage(int pageCode, int pageSize) throws Exception {
		if(pageCode<=0)
		{
			pageCode=1;
		}
		PageBean2 pb = new PageBean2();
		TeacherAccount teacherAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(userId) from users where identity = 2";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);

		sql = "SELECT teacherId,userName, teacherName, teacherPhoneNumber FROM teacher,users where userId=teacherId ORDER BY teacherId limit ?,?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, (pageCode-1)*pageSize);
		pstmt.setInt(2, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String teacherId = rs.getString(1);
			String userName = rs.getString(2);
			String teacherName = rs.getString(3);
			String teacherPhoneNumber = rs.getString(4);
			teacherAccount = new TeacherAccount(teacherId,userName,teacherName, teacherPhoneNumber);
			pb.getList().add(teacherAccount);
		}
		return pb;
	}
	//显示具体老师信息
	@RequestMapping("/showCertainTeacher")
	public PageBean2 findByPage2(int pageCode, int pageSize,String str) throws Exception {
		PageBean2 pb = new PageBean2();
		TeacherAccount teacherAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(userId) from users where identity = 2";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);
        
		sql = "SELECT userId,userName, teacherName, teacherPhoneNumber FROM teacher,users where users.userId=teacher.teacherId and teacher.teacherName=? limit ?,?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, str);
		pstmt.setInt(2, (pageCode-1)*pageSize);
		pstmt.setInt(3, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String userId = rs.getString(1);
			String userName = rs.getString(2);
			String teacherName = rs.getString(3);
			String teacherPhoneNumber = rs.getString(4);
			teacherAccount = new TeacherAccount(userId,userName,teacherName, teacherPhoneNumber);
			pb.getList().add(teacherAccount);
		}
		return pb;
	}
	//更新老师信息
	public boolean updateTeacher(String showT, ShowTeachers showTeachers)
			throws SQLException {
		boolean flag = false;
		conn = DBLink.getConnection();
	//	String sql = "update teacher set teacherId=?, teacherPhoneNumber=? where teacherName in(" + showTeachers.getTeacherName() + ")" ;
		String sql = "update teacher set teacherId=?, teacherPhoneNumber=? where teacherName=?" ;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, showTeachers.getUserId());
			//pstmt.setString(2, showTeachers.getUserName());
			pstmt.setString(2, showTeachers.getTeacherPhoneNumber());
			pstmt.setString(3,showTeachers.getTeacherName());
			int rows = pstmt.executeUpdate(sql);		
			if (rows == 1) {
				flag = true;
				System.out.println("修改成功");
			}
		} catch (Exception e) {
			System.out.println("修改失败");
			e.printStackTrace();
		}finally{
			close();
		}
		return flag;
	}
	//删除老师信息
	public boolean deleteTeacherInfo(String teacherId ) {
		boolean flag = false;
		conn = DBLink.getConnection();
		String sql = "delete from teacher where teacherId in(" + teacherId + ")";
		try {
			pstmt = conn.prepareStatement(sql);
			int rows = pstmt.executeUpdate(sql);
			if (rows > 0)
			{
				flag = true;
				System.out.println(flag);
				System.out.println("老师信息删除成功");
			} else {
				System.out.println("删除失败");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}	
}
